#!/bin/bash

APP=edu2077

if [ -n "$2" ] ;then
   do_date=$2
else 
   echo "请传入日期参数"
   exit
fi 

dim_user_zip="
insert overwrite table ${APP}.dim_user_zip partition (dt='9999-12-31')
select
    data.id,
    data.login_name,
    data.nick_name,
    md5(data.real_name),
    md5(if(data.phone_num regexp '^(13[0-9]|14[01456879]|15[0-35-9]|16[2567]|17[0-8]|18[0-9]|19[0-35-9])\\d{8}$',data.phone_num,null)),
    md5(if(data.email regexp '^[a-zA-Z0-9_-]+@[a-zA-Z0-9_-]+(\\.[a-zA-Z0-9_-]+)+$',data.email,null)),
    data.user_level,
    data.birthday,
    data.gender,
    data.create_time,
    data.operate_time,
    '2022-02-28' start_date,
    '9999-12-31' end_date
from ods_user_info_inc
where dt='2022-02-28'
and type='bootstrap-insert';
"

dim_chapter_full="
insert overwrite table ${APP}.dim_chapter_full partition(dt='$do_date')
select
       k1.chapter_id,
       k1.chapter_name,
       k1.publisher_id,
       k1.is_free,
       k1.create_time,
       k1.update_time,
       k2.course_id,
       k2.course_name,
       k2.subject_id,
       k2.subject_name,
       k2.category_id,
       k2.category_name
from
(
    select
        id chapter_id,
        chapter_name,
        publisher_id,
        is_free,
        create_time,
        update_time,
        course_id
    from ods_chapter_info_full
) k1
join
(
    select
        t1.course_id,
        t1.course_name,
        t2.subject_id,
        t2.subject_name,
        t3.category_id,
        t3.category_name
    from
    (
        select id course_id,
               course_name,
               subject_id
        from ods_course_info_full
        where dt = '2022-02-28'
    ) t1
    join
    (
        select
            id subject_id,
            subject_name ,
            category_id
        from ods_base_subject_info_full
    ) t2
    on t1.subject_id = t2.subject_id
    join
    (
        select
            id category_id,
            category_name
        from ods_base_category_info_full
    ) t3
    on t2.category_id = t3.category_id
) k2
on k1.course_id = k2.course_id;
"

dim_course_full="
insert overwrite table ${APP}.dim_course_full partition(dt='$do_date')
select distinct
	t5.id,
	course_name,
	course_slogan,
	publisher_id,
	origin_price,
	reduce_amount,
	actual_price,
	create_time,
	teacher,
	t6.subject_id,
	t6.subject_name,
	t6.category_id,
	t6.category_name,
	chapter_num,
    chapter_name,
	t7.video_id,
	t7.video_name,
	t7.during_sec,
    w1.paper_id,
    w1.paper_title
from
(
	select
		id,
		course_name,
		course_slogan,
		publisher_id,
		origin_price,
		reduce_amount,
		actual_price,
		create_time,
		teacher,
		subject_id,
		chapter_num
	from ods_course_info_full
	where dt='2022-02-28'
) t5
join
(
	select
		t1.id as subject_id,
		t1.subject_name,
		category_id,
		category_name
	from
	(
		select
			id,
			subject_name,
			category_id
		from ods_base_subject_info_full
		where dt='2022-02-28'
	) t1
	join
	(
		select
			id,
			category_name
		from ods_base_category_info_full
		where dt='2022-02-28'
	) t2
	on t1.category_id = t2.id
) t6
on t5.subject_id = t6.subject_id
right join
(
	select
		course_id,
		t3.chapter_name,
		video_id,
		video_name,
		during_sec
	from
	(
		select
			id,
		    chapter_name,
			course_id,
			video_id
		from ods_chapter_info_full
		where dt='2022-02-28'
	) t3
	join
	(
		select
			id ,
			video_name,
			during_sec
		from ods_video_info_full
		where dt='2022-02-28'
	) t4
	on t3.video_id = t4.id
) t7
on t5.id = t7.course_id
join
(
    select
        id paper_id,
        paper_title,
        course_id
    from ods_test_paper_full
) w1
on w1.course_id = t5.id;
"

dim_province_full="
insert overwrite table ${APP}.dim_province_full partition(dt='$do_date')
select
    id,
    name,
    region_id,
    area_code,
    iso_code,
    iso_3166_2
from ods_base_province_full;
"

dim_question_full="
insert overwrite table ${APP}.dim_question_full partition(dt='$do_date')
select
    k3.id,
    k3.score,
    k3.publisher_id,
    k3.create_time,
    k3.paper_id,
    k3.paper_title,
    k4.question_id,
    k4.question_txt,
    k4.question_type,
    k4.option_id,
    k4.option_txt,
    k4.is_correct
from
(
    select
           k1.id,
           k1.score,
           k1.publisher_id,
           k1.create_time,
           k1.question_id,
           k1.paper_id,
           k2.paper_title
    from
    (
        select id,
               score,
               publisher_id,
               create_time,
               paper_title paper_id,
               question_id
        from ods_test_paper_question_full
        where dt = '2022-02-28'
    ) k1
    join
    (
        select
            id,
            paper_title
        from ods_test_paper_full
        where dt = '2022-02-28'
    ) k2
    on k1.paper_id = k2.id
) k3
join
(
    select
           t1.id question_id,
           question_txt,
           question_type,
           option_id,
           option_txt,
           is_correct
    from
    (
        select id,
               point_id question_txt,
               question_type
        from ods_test_question_info_full
        where dt = '2022-02-28'
    ) t1
    left join
    (
        select
            id option_id,
            option_txt,
            question_id,
            is_correct
        from ods_test_question_option_full
        where dt = '2022-02-28'
    ) t2
    on t1.id = t2.question_id
) k4
on k3.question_id = k4.question_id;
"

case $1 in
"dim_user_zip")
    hive -e "$dim_user_zip"
;;
"dim_chapter_full")
    hive -e "$dim_chapter_full"
;;
"dim_course_full")
    hive -e "$dim_course_full"
;;
"dim_province_full")
    hive -e "$dim_province_full"
;;
"dim_question_full")
    hive -e "$dim_question_full"
;;
"all")
    hive -e "$dim_user_zip$dim_chapter$dim_course_full$dim_province_full$dim_question_full"
;;
esac